package com.oswego.dawcs;

/**
 * Updated by Marcus on 5/2/14.
 */

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHandler extends SQLiteOpenHelper {

    // All Static variables
    // Database Version
    private static final int DATABASE_VERSION = 1;

    // Database Name
    private static final String DATABASE_NAME = "DAWCS";

    // table names
    private static final String TABLE_CURRENT = "CURRENT_SETTING";
    private static final String TABLE_CURRENT_LAYOUT = "CURRENT_LAYOUT"; //RELATION
    private static final String TABLE_LAYOUT = "LAYOUT";
    private static final String TABLE_LAYOUT_CHANNEL = "LAYOUT_CHANNEL"; //RELATION
    private static final String TABLE_CHANNEL = "CHANNEL";
    private static final String TABLE_CHANNEL_COMPONENT = "CHANNEL_COMPONENT"; //RELATION
    private static final String TABLE_COMPONENT = "COMPONENT";

    // Current Setting Table Columns names
    private static final String PROFILE_ID = "ProfileID";
    //private static final String LAYOUT_ID = "LayoutID";
    //private static final String PRESET_ID = "PresetID";

    // Layout Table Columns names
    private static final String LAYOUT_ID = "LayoutID";
    private static final String NAME = "Name";
    private static final String THEME = "Theme";

    // Channel Table Column names
    private static final String CHANNEL_ID = "ChannelID";
    private static final String CHANNEL_NUMBER = "Number";
    private static final String CHANNEL_GROUP = "Group";


    // Component Table Columns names
    private static final String COMPONENT_ID = "ComponentID";
    private static final String POSITION = "Position";
    private static final String TYPE = "Type";
    private static final String VALUE = "Value";

    //---------------------------------------------------
    // Because the relations use columns that are already
    // established in previous tables, there is no need
    // to declare their names again.
    //---------------------------------------------------


    // Creation of SQL statements for creation of Tables

    String CREATE_CURRENT_SETTING_TABLE = "CREATE TABLE " + TABLE_CURRENT + "("
            + PROFILE_ID + " INTEGER PRIMARY KEY,"
            + LAYOUT_ID + " INTEGER,"
            //+ PRESET_ID + " STRING" +
            + ")";

    String CREATE_CURRENT_LAYOUT_TABLE = "CREATE TABLE " + TABLE_CURRENT_LAYOUT + "("
            + PROFILE_ID + " INTEGER PRIMARY KEY,"
            + LAYOUT_ID + " INTEGER" + ")";

    String CREATE_LAYOUT_TABLE = "CREATE TABLE " + TABLE_LAYOUT + "("
            + LAYOUT_ID + " INTEGER PRIMARY KEY,"
            + NAME + " TEXT, "
            + THEME + " INTEGER" + ")";

    String CREATE_LAYOUT_CHANNEL_TABLE = "CREATE TABLE " + TABLE_LAYOUT_CHANNEL + "("
            + LAYOUT_ID + " INTEGER PRIMARY KEY,"
            + CHANNEL_ID + " INTEGER" + ")";

    String CREATE_CHANNEL_TABLE = "CREATE CHANNEL " + TABLE_CHANNEL + "("
            + CHANNEL_ID  + " INTEGER PRIMARY KEY,"
            + CHANNEL_NUMBER + " INTEGER,"
            + CHANNEL_GROUP + " INTEGER" +")";

    String CREATE_CHANNEL_COMPONENT_TABLE = "CREATE TABLE " + TABLE_CHANNEL_COMPONENT + "("
            + LAYOUT_ID + " INTEGER PRIMARY KEY,"
            + CHANNEL_ID + " INTEGER" + ")";

    String CREATE_COMPONENT_TABLE = "CREATE TABLE " + TABLE_COMPONENT + "("
            + COMPONENT_ID + " INTEGER PRIMARY KEY,"
            + POSITION + " TEXT,"
            + TYPE + " TEXT,"
            + VALUE + " INTEGER,"
            + ")";

    public DatabaseHandler(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    // Create Tables
    @Override
    public void onCreate(SQLiteDatabase db) {

        db.execSQL(CREATE_CURRENT_SETTING_TABLE);
        db.execSQL(CREATE_CURRENT_LAYOUT_TABLE);
        db.execSQL(CREATE_LAYOUT_TABLE);
        db.execSQL(CREATE_LAYOUT_CHANNEL_TABLE);
        db.execSQL(CREATE_CHANNEL_TABLE);
        db.execSQL(CREATE_CHANNEL_COMPONENT_TABLE);
        db.execSQL(CREATE_COMPONENT_TABLE);

    }

    // Upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_CURRENT);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_CURRENT_LAYOUT);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_LAYOUT);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_LAYOUT_CHANNEL);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_CHANNEL);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_CHANNEL_COMPONENT);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_COMPONENT);

        // Create tables again
        onCreate(db);
    }

//--------------------------------------------
//  CRUD Stuff --- Create, Read, Update, Delete
//--------------------------------------------

    //add profile
    void addProfile(CurrentState state, int layout_id) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();

        //values.put(PROFILE_ID, state.getProfile());  --Autoincrement will be used
                                                       // as long as no value is entered
                                                       // for PROFILE_ID
        values.put(LAYOUT_ID, state.getLayout());


        // Inserting Row
        int state_id = (int)db.insert(TABLE_CURRENT, null, values);

        // automatically creates the relation

            createCurrentLayout(state_id, layout_id);

        db.close(); // Closing database connection
    }


    void addChannel(DBChannel channel, int component_id){
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();

        //values.put(CHANNEL_ID, channel.getID());
        values.put(CHANNEL_NUMBER, channel.getChannel());
        values.put(CHANNEL_GROUP, channel.getGroup());

        // Inserting Row
        // Inserting Row
        int channel_id = (int)db.insert(TABLE_CHANNEL, null, values);

        // automatically creates the relation

            createCurrentLayout(channel_id, component_id);


        db.close (); // Closing database connection
    }


    //-------------------------------------------------------------------
    // Add new component
    // This method is currently designed to add 5 records to the
    // component table.
    // one component for each type.
    //-------------------------------------------------------------------

    void addComponent(Component component) {
        SQLiteDatabase db = this.getWritableDatabase();

        String x = "";

        for(int i = 0; i < 5; i ++){

            ContentValues values = new ContentValues();

            switch (i){
                case 0:
                    x = "EQ_HIGH";
                    break;
                case 1:
                    x = "EQ_MID";
                    break;
                case 2:
                    x = "EQ_LOW";
                    break;
                case 3:
                    x = "PAN";
                    break;
                case 4:
                    x = "FADE";
                default:
                    break;
            }

            //values.put(COMPONENT_ID, component.getId()  + i);
            values.put(POSITION, component.getPosition());
            values.put(TYPE, x);
            values.put(VALUE, component.getValue());

        // Inserting Row
            db.insert(TABLE_COMPONENT, null, values);
        }
        db.close(); // Closing database connection
    }

    // Add new layout
    void addLayout(Layout layout, int channel_id) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();

        //values.put(LAYOUT_ID, layout.getID());
        values.put(NAME, layout.getName());
        values.put(THEME, layout.getTheme());

        // Inserting Row
        int layout_id = (int)db.insert(TABLE_LAYOUT, null, values);

        // automatically creates the relation

            createCurrentLayout(layout_id, channel_id);

        db.close();
    }


    //--------------END of ADDs-------------------------------------

    //-------------Start of single record Queries-------------------

    // Getting single profile
    CurrentState getProfile(String id) {
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.query(TABLE_CURRENT, new String[] { PROFILE_ID, LAYOUT_ID }, PROFILE_ID + "=?",
                new String[] { id }, null, null, null, null);
        if (cursor != null)
            cursor.moveToFirst();

        CurrentState state = new CurrentState(Integer.parseInt(cursor.getString(0)),
                Integer.parseInt(cursor.getString(1)));
        // return contact
        return state;
    }

    DBChannel getChannel(String id) {
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.query(TABLE_CHANNEL, new String[] { CHANNEL_ID, CHANNEL_NUMBER,
                CHANNEL_GROUP}, PROFILE_ID + "=?",
                new String[] { id }, null, null, null, null);
        if (cursor != null)
            cursor.moveToFirst();

        DBChannel channel = new DBChannel(cursor.getInt(0),
                cursor.getInt(1), cursor.getInt(2));
        // return contact
        return channel;
    }



    // Getting single layout
    Layout getLayout(String id) {
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.query(TABLE_LAYOUT, new String[] { LAYOUT_ID,
                NAME, THEME  }, COMPONENT_ID + "=?",
                new String[] { id }, null, null, null, null);
        if (cursor != null)
            cursor.moveToFirst();

        Layout layout = new Layout(cursor.getInt(0), cursor.getString(1),
                cursor.getInt(2));
        // return layout
        return layout;
    }

    // Getting single component
    Component getComponent(String id) {
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.query(TABLE_COMPONENT, new String[] { COMPONENT_ID, POSITION,
                TYPE, VALUE }, COMPONENT_ID + "=?",
                new String[] { id }, null, null, null, null);
        if (cursor != null)
            cursor.moveToFirst();

        Component component = new Component(cursor.getInt(0),
                cursor.getString(1), cursor.getString(2), cursor.getInt(3));
        // return contact
        return component;
    }

    //-------------------End of single record queries-------------------------------------

    //-------------------Beginning of Full table queries----------------------------------

    // Getting All layouts
    public List<Layout> getAllLayouts() {
        List<Layout> layoutList = new ArrayList<Layout>();
        // Select All Query
        String selectQuery = "SELECT  * FROM " + TABLE_LAYOUT;

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                Layout layout = new Layout();
                layout.setID(cursor.getInt(0));
                layout.setTheme(cursor.getInt(1));
                // Adding contact to list
                layoutList.add(layout);
            } while (cursor.moveToNext());
        }


        return layoutList;
    }

    // Getting All Channels
    public List<DBChannel> getAllComponents() {
        List<DBChannel> channelList = new ArrayList<DBChannel>();
        // Select All Query
        String selectQuery = "SELECT  * FROM " + TABLE_CHANNEL;

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                DBChannel channel = new DBChannel();
                channel.setID(cursor.getInt(0));
                channel.setChannel(cursor.getInt(1));
                channel.setGroup(cursor.getInt(2));
                // Adding component to list
                channelList.add(channel);
            } while (cursor.moveToNext());
        }


        return channelList;
    }


    // Getting All Profiles
    public List<CurrentState> getAllProfiles() {
        List<CurrentState> profileList = new ArrayList<CurrentState>();
        // Select All Query
        String selectQuery = "SELECT  * FROM " + TABLE_COMPONENT;

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                CurrentState state = new CurrentState();
                state.setProfile(cursor.getInt(0));
                state.setLayout(cursor.getInt(1));

                // Adding component to list
                profileList.add(state);
            } while (cursor.moveToNext());
        }


        return profileList;
    }

    //-------------------end of full table query---------------------

    //-------------------Beginning of updates------------------------

    // Updating single layout
    public int updateLayout(Layout layout) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(LAYOUT_ID, layout.getID());
        values.put(THEME, layout.getTheme());


        // updating row
        return db.update(TABLE_LAYOUT, values, LAYOUT_ID + " = ?",
                new String[] { String.valueOf(layout.getID()) });
    }

    // Updating single component
    public int updateComponent(Component component) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(COMPONENT_ID, component.getId());
        values.put(VALUE, component.getValue());


        // updating row
        return db.update(TABLE_COMPONENT, values, COMPONENT_ID + " = ?",
                new String[] { String.valueOf(component.getId()) });
    }

    public int updateChannel(DBChannel channel) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(CHANNEL_ID, channel.getID());
        values.put(CHANNEL_NUMBER, channel.getChannel());
        values.put(CHANNEL_GROUP, channel.getGroup());


        // updating row
        return db.update(TABLE_COMPONENT, values, COMPONENT_ID + " = ?",
                new String[] { String.valueOf(channel.getID()) });
    }

    // Updating single profile
    public int updateProfile(CurrentState state) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(PROFILE_ID, state.getProfile());
        values.put(LAYOUT_ID, state.getLayout());


        // updating row
        return db.update(TABLE_CURRENT, values, COMPONENT_ID + " = ?",
                new String[] { String.valueOf(state.getProfile()) });
    }

    //--------------------end of updates---------------------------------

    //--------------beginning of deleting single records------------------

    // Deleting single layout
    public void deleteLayout(Layout layout) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_LAYOUT, LAYOUT_ID + " = ?",
                new String[] { String.valueOf(layout.getID()) });
        db.close();
    }

    // Deleting Channel
    public void deleteLayout(DBChannel channel) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_CHANNEL, CHANNEL_ID + " = ?",
                new String[] { String.valueOf(channel.getID()) });
        db.close();
    }

    // Deleting single component
    public void deleteComponent(Component component) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_COMPONENT, COMPONENT_ID + " = ?",
                new String[] { String.valueOf(component.getId()) });
        db.close();
    }

    public void deleteProfile(CurrentState component) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_CURRENT, PROFILE_ID + " = ?",
                new String[] { String.valueOf(component.getProfile()) });
        db.close();
    }

//---------------------end of deleting records ----------------

//-------------------beginning of table count------------------

    // Getting component Count
    public int getLayoutCount() {
        String countQuery = "SELECT  * FROM " + TABLE_LAYOUT;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        cursor.close();

        // return count
        return cursor.getCount();
    }

    // Getting channel Count
    public int getChannelCount() {
        String countQuery = "SELECT  * FROM " + TABLE_CHANNEL;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        cursor.close();

        // return count
        return cursor.getCount();
    }

    // Getting component Count
    public int getComponentCount() {
        String countQuery = "SELECT  * FROM " + TABLE_COMPONENT;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        cursor.close();

        // return count
        return cursor.getCount();
    }

    public int getProfileCount() {
        String countQuery = "SELECT  * FROM " + TABLE_CURRENT;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        cursor.close();

        // return count
        return cursor.getCount();
    }

    //----------------end of count queries--------------------------------

    //---------------- Beginning of relations -------------------------

    // These methods are to be used when creating a relation between
    // two of the major tables.
    // These relations can be retrieved from the tables
    // CURRENT_LAYOUT, LAYOUT_CHANNEL, AND CHANNEL_COMPONENT

    //----------------Creating a relation------------------------------

    public long createCurrentLayout(int prof_id, int lay_id) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(PROFILE_ID, prof_id );
        values.put(LAYOUT_ID, lay_id);

        long id = db.insert(TABLE_CURRENT_LAYOUT, null, values);

        return id;
    }

    public long createLayoutChannel(int lay_id, int chan_id) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(LAYOUT_ID, lay_id);
        values.put(CHANNEL_ID, chan_id );

        long id = db.insert(TABLE_LAYOUT_CHANNEL, null, values);

        return id;
    }

    public long createChannelComponent(int chan_id, int comp_id) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(CHANNEL_ID, chan_id );
        values.put(COMPONENT_ID, comp_id );

        long id = db.insert(TABLE_LAYOUT_CHANNEL, null, values);

        return id;
    }
    //---------------------end of relation creation-----------------------

    //---------------------beginning of relation updates-------------------

    // I'll do this if we ever find a need to... or if I ever feel like
    // not being lazy

    //--------------------end of relation updates--------------------------

    //------------------beginning of relation deletions-------------------

    //WARNING!!! I'M NOT SURE THESE WILL WORK IN CURRENT STATE!!!!!
    public void deleteCurrentLayout(long id) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_CURRENT_LAYOUT, PROFILE_ID + " = ?",
                new String[] { String.valueOf(id) });
    }

    public void deleteLayoutChannel(long id) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_LAYOUT_CHANNEL, LAYOUT_ID + " = ?",
                new String[] { String.valueOf(id) });
    }

    public void deleteChannelComponent(long id) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_CHANNEL_COMPONENT, CHANNEL_ID + " = ?",
                new String[] { String.valueOf(id) });
    }

    //------------------end of relations-------------------------------------

    //-------------------CLOSE THE DATABASE!!!-----------------------------

    public void closeDB() {
        SQLiteDatabase db = this.getReadableDatabase();
        if (db != null && db.isOpen())
            db.close();
    }

    //-----------------Queue for Channel Data ------------------------------

    public List<ChannelData> getAllChannelData(Layout template){

        SQLiteDatabase db = this.getReadableDatabase();

        List<ChannelData> templateList = new ArrayList<ChannelData>();

        // Get ID from template
        int TID = template.getID();

        //variables reserved for component values
        int a =0, b =0, c = 0, d = 0, e = 0;

        //get all IDs of Channels associated with template
        String channelQuery =
                "SELECT " + CHANNEL_ID +
                " FROM " + TABLE_LAYOUT_CHANNEL +
                " WHERE " + LAYOUT_ID + " = " + TID;

        /**
        String componentQuery =
                "SELECT " + COMPONENT_ID +
                "FROM " + TABLE_CHANNEL_COMPONENT +
                "WHERE " + CHANNEL_ID + " = " +
        */

        //creates cursor to navigate the channel IDs query;
        Cursor channelCursor = db.rawQuery(channelQuery, null);


        for(int i = 0; i < channelCursor.getCount(); i++){

            //grab group and channel number from current channel
            String groupQuery =
                    "SELECT " + CHANNEL_NUMBER + ", " +
                            CHANNEL_GROUP + " FROM " +
                            TABLE_CHANNEL + " WHERE " +
                            CHANNEL_ID + " = " +
                            channelCursor.getInt(0);

            Cursor groupCursor = db.rawQuery(groupQuery, null);

            // grab components associated with current channel
            String componentQuery =
                   "SELECT " + COMPONENT_ID +
                   "FROM " + TABLE_CHANNEL_COMPONENT +
                   "WHERE " + CHANNEL_ID + " = " +
                   channelCursor.getInt(0);

            Cursor componentCursor = db.rawQuery(componentQuery, null);

            //retrieve all values from each component
            String valueQuery =
                    "SELECT " + VALUE + "FROM " + TABLE_COMPONENT +
                    "WHERE " + COMPONENT_ID + " = " +
                    componentCursor.getInt(0);

            Cursor valueCursor = db.rawQuery(valueQuery, null);


            //cycle through each component and assign the values
            // to variables a through e
            for(int j =0; j < 5; j++){

                switch (j){
                    case 0:
                        a = valueCursor.getInt(0);
                        valueCursor.moveToNext();
                        break;
                    case 1:
                        b =  valueCursor.getInt(0);
                        valueCursor.moveToNext();
                        break;
                    case 2:
                        c =  valueCursor.getInt(0);
                        valueCursor.moveToNext();
                        break;
                    case 3:
                        d =  valueCursor.getInt(0);
                        valueCursor.moveToNext();
                        break;
                    case 4:
                        e =  valueCursor.getInt(0);
                        valueCursor.moveToNext();
                        break;
                    default:
                        break;
                }
            }


            //add to templateList
            templateList.add(new ChannelData(
                    groupCursor.getInt(0),  // channel number
                    groupCursor.getInt(1),  // group number
                    a,                      // EQ hi
                    b,                      // EQ mid
                    c,                      // EQ low
                    d,                      // pan
                    e));                    // fader

            //move to next channel
            channelCursor.moveToNext();

        }

        return templateList;
    }


    //------------------END OF databaseHANDLER-----------------------------
    // Text me with any issues (585) 402-2077 - Marcus

}